"""
Case Type   : VIEW
Case Name   : 查询系统视图pg_stat_activity，定位函数嵌套正在执行的sql
Create At   : 2024/10/8
Owner       : lonely-dance
Description :
    1.创建函数嵌套
    2.调用函数
    3.查询系统视图pg_stat_activity
    4.清理环境
Expect      :
    1.创建函数成功
    2.调用函数成功
    3.pg_stat_activity显示调用函数正在执行的sql
    4.清理环境成功
History     :
"""
import time
import unittest
import os
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.ComThread import ComThread
from yat.test import Node
from yat.test import macro

primary_sh = CommonSH('PrimaryDbUser')

@unittest.skipIf(3 != primary_sh.get_node_num(), '非1+2环境不执行')
class Analysis(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'---{os.path.basename(__file__)} start---')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.standby1_sh = CommonSH('Standby1DbUser')
        self.p_node = Node('PrimaryDbUser')
        self.s1_node = Node('Standby1DbUser')
        self.constant = Constant()

    def test_Analysis(self):
        text = '-----step1:创建函数 expect:成功-----'
        self.log.info(text)
        func1_cmd = '''
            DROP FUNCTION if exists test1;
            CREATE FUNCTION test1(delay integer) 
            RETURNS void AS 
                'SELECT pg_sleep(delay); '
            LANGUAGE SQL 
            IMMUTABLE 
            RETURNS NULL ON NULL INPUT;
        '''
        self.log.info(func1_cmd)

        func2_cmd = '''
            DROP FUNCTION if exists test0010;
            CREATE FUNCTION test0010(delay integer) 
            RETURNS void AS 
                'SELECT test1(delay);'
            LANGUAGE SQL 
            IMMUTABLE 
            RETURNS NULL ON NULL INPUT;
        '''
        self.log.info(func2_cmd)

        func1_res = self.primary_sh.execut_db_sql(func1_cmd)
        func2_res = self.primary_sh.execut_db_sql(func2_cmd)
        self.log.info(func1_res)
        self.log.info(func2_res)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, func1_res, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, func2_res, '执行失败:' + text)

        text = '-----step2:调用函数 expect:成功-----'
        self.log.info(text)
        sql_cmd1 = f'''source {macro.DB_ENV_PATH};
                gsql -d {self.p_node.db_name} -p {self.p_node.db_port} -c "call test0010(10);";'''
        self.log.info(sql_cmd1)
        sh_thread1 = ComThread(self.p_node.sh, args=(sql_cmd1,))
        sh_thread1.setDaemon(True)
        sh_thread1.start()

        time.sleep(5)
        text = '-----step3:查询系统视图pg_stat_activity expect:成功-----'
        self.log.info(text)
        sql_cmd = f'''select * from pg_stat_activity where datname = '{self.p_node.db_name}' ;'''
        sql_result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn('test0010', sql_result, '执行失败:' + text)

        sh_thread1.join(120)

    def tearDown(self):
        text = '-----step4:清理环境 expect:成功-----'
        self.log.info(text)

        sql_cmd = '''
        DROP FUNCTION if exists test1;
        DROP FUNCTION if exists test0010;
        '''
        self.log.info(sql_cmd)

        sql_result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, sql_result, '执行失败:' + text)



